﻿GO
CREATE TABLE dbo.bht_Groups
	(
	Id int NOT NULL,
	Name nvarchar(400) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.bht_Groups ADD CONSTRAINT
	PK_bht_Groups PRIMARY KEY CLUSTERED 
	(
	Id
	)  ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupSave]
	@Id				INT,
	@Name			NVARCHAR(400),
	@SystemName		NVARCHAR(400),
	@User			INT = 7000001
AS
BEGIN

	DECLARE
		@Sql	NVARCHAR(MAX)

	IF @Id = 0
	BEGIN
		BEGIN TRAN
		
		-- Dodanie w sTree
		INSERT INTO [sTree]
			   ([del]
			   ,[parent]
			   ,[name]
			   ,[typ]
			   ,[ctime]
			   ,[cuser])
		 VALUES
			   (0
			   ,0
			   ,@SystemName
			   ,'T'
			   ,GETDATE()
			   ,@User)

		SELECT @Id = SCOPE_IDENTITY()
		
		-- Dodanie w bht_Groups
		INSERT INTO bht_Groups VALUES (@Id, @Name)
		
		-- Dodanie tabeli T
		SET @Sql = 'CREATE TABLE [dbo].[T' + CAST(@Id as NVARCHAR(10)) + '](
						[idx] [int] IDENTITY(1,1) NOT NULL,
						[del] [bit] NOT NULL,
						[identyfikator] [nvarchar](200) NULL,
						[ctime] [datetime] NULL,
						[cuser] [int] NULL,
						[mtime] [datetime] NULL,
						[muser] [int] NULL
					 CONSTRAINT [PK__T' + CAST(@Id as NVARCHAR(10)) + '_idx] PRIMARY KEY CLUSTERED 
					(
						[idx] ASC
					) 
					) ON [PRIMARY]'
		
		EXEC sp_executesql @Sql

		SET @Sql = 'ALTER TABLE [dbo].[T' + CAST(@Id as NVARCHAR(10)) + '] ADD  CONSTRAINT [DF_T' + CAST(@Id as NVARCHAR(10)) + '_del]  DEFAULT (0) FOR [del]'
		
		EXEC sp_executesql @Sql



		
		-- Dodanie tabeli R
		SET @Sql = 'CREATE TABLE [dbo].[R' + CAST(@Id as NVARCHAR(10)) + '](
						[idx] [int] NOT NULL,
						[del] [bit] NOT NULL,
						[rel] [int] NOT NULL,
						[ctime] [datetime] NULL,
						[cuser] [int] NULL,
						[mtime] [datetime] NULL,
						[muser] [int] NULL
					) ON [PRIMARY]'
		
		EXEC sp_executesql @Sql

		SET @Sql = 'ALTER TABLE [dbo].[R' + CAST(@Id as NVARCHAR(10)) + '] ADD  DEFAULT (0) FOR [del]'
		EXEC sp_executesql @Sql
		
		SET @Sql = 'ALTER TABLE [dbo].[R' + CAST(@Id as NVARCHAR(10)) + '] ADD  DEFAULT (' + CAST(@Id as NVARCHAR(10)) + ') FOR [rel]'
		EXEC sp_executesql @Sql
		
		-- Dodanie widoku V
		SET @Sql = 'CREATE VIEW [dbo].[' + @SystemName + '] AS
						SELECT * FROM T' + CAST(@Id as NVARCHAR(10)) + ' WHERE del = 0'
		EXEC sp_executesql @Sql
		
		
		COMMIT
	
	END
	ELSE
	BEGIN
	
		DECLARE
			@OldSysName		NVARCHAR(400)
	
		BEGIN TRAN
		
		SELECT @OldSysName = 'dbo.' + name FROM sTree WHERE id = @Id
		
		UPDATE bht_Groups SET Name = @Name WHERE Id = @Id
		UPDATE sTree SET name = @SystemName, mtime = GETDATE(), muser = @User WHERE id = @Id
		
		SET @SystemName = 'dbo.' + @SystemName
		
		EXEC sp_rename @OldSysName , @SystemName;
	
		COMMIT
	
	END

	SELECT @Id
END
GO


